-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO UPDATE BATCH OUT EFX TABLE



\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION insert_funcs_batch_efx_t() RETURNS varchar AS $$
DECLARE
	v_efx_append varchar(1);
	v_cust_id integer;
BEGIN

--SELECT INTO bt name_type from batch_insurance0_t;


-- STEP 1 FIRST INSERTION INTO BATCH_OUT_EFX TABLE 
--    LOGIC:  which name to send to efx and define name_type = 'G'


  SELECT INTO v_cust_id cust_id FROM batch_insurance0_t;
  SELECT INTO v_efx_append efx_append FROM insurance0_treatment_t WHERE cust_id = v_cust_id;

  IF v_efx_append = 'Y' THEN

	RAISE NOTICE 'START FIRST INSERTION INTO BATCH_OUT_EFX TABLE';

	INSERT INTO insurance0_batch_efx_out_t
	SELECT '',
 		'1',
		(CASE WHEN a.g_mname IS NOT NULL THEN a.g_fname || ' ' || a.g_mname || ' ' ||a.g_lname
                      ELSE a.g_fname || ' ' ||a.g_lname
                      END) as personal,
		a.g_addr1,
		a.g_city,
		a.g_state,
		a.g_zip5,
		a.g_zip4,
		'',
		a.acctnum,
		a.hid,
		a.name_type,
		'',
		a.cust_id::varchar(4),
		'',
		a.g_ssn,
		(CASE WHEN a.g_dob IS NULL THEN NULL 
                      ELSE a.day_dob::varchar(2)||a.month_dob::varchar(2)||date_part('year',a.g_dob)::varchar(4)
                      END) as dob,
                a.month_rdate::varchar(2)||a.day_rdate::varchar(2)||a.year_rdate::varchar(2)||'_'||
                          a.hour_rdate::varchar(2)||a.min_rdate::varchar(2)||a.sec_rdate::varchar(2)||'_'||a.cid::varchar(6) as filestamp,
		''
		FROM (
                       SELECT
                           *,
                           (CASE WHEN date_part('second',our_run_date) < 10 then '0'||date_part('second',our_run_date)::varchar(1)
                                 ELSE                                            date_part('second',our_run_date)::varchar(2)
                                 END) as sec_rdate,
                           (CASE WHEN date_part('minute',our_run_date) < 10 then '0'||date_part('minute',our_run_date)::varchar(1)
                                 ELSE                                           date_part('minute',our_run_date)::varchar(2)
                                 END) as min_rdate,
                           (CASE WHEN date_part('hour',our_run_date) < 10 then '0'||date_part('hour',our_run_date)::varchar(1)
                                 ELSE                                           date_part('hour',our_run_date)::varchar(2)
                                 END) as hour_rdate,
                           (CASE WHEN date_part('day',our_run_date) < 10 then '0'||date_part('day',our_run_date)::varchar(1) 
                                 ELSE                                         date_part('day',our_run_date)::varchar(2)
                                 END) as day_rdate,
                           (CASE WHEN date_part('month',our_run_date) < 10 then '0'||date_part('month',our_run_date)::varchar(1)
                                 ELSE                                           date_part('month',our_run_date)::varchar(2)
                                 END) as month_rdate,
                           (date_part('year',our_run_date) - 2000) as year_rdate,
                           (CASE WHEN cust_id < 10     then '00000'||cust_id::varchar(1)
                                 WHEN cust_id < 100    then '0000'||cust_id::varchar(2)
                                 WHEN cust_id < 1000   then '000'||cust_id::varchar(3)
                                 WHEN cust_id < 10000  then '00'||cust_id::varchar(4)
                                 WHEN cust_id < 100000 then '0'||cust_id::varchar(5)
                                 ELSE                       cust_id::varchar(6)
                                 END) as cid,
                           (CASE WHEN date_part('day',g_dob) < 10 then '0'||date_part('day',g_dob)::varchar(1)
                                 ELSE                                  date_part('day',g_dob)::varchar(2)
                                 END) as day_dob,
                           (CASE WHEN date_part('month',g_dob) < 10 then '0'||date_part('month',g_dob)::varchar(1)
                                 ELSE                                    date_part('month',g_dob)::varchar(2)
                                 END) as month_dob

                       FROM
                          batch_insurance0_t
		       WHERE
			  ( 
                           (emdeon_commercial_flag = 'C'   OR emdeon_tpl_flag = 'C')
			    AND   				
			    name_type = 'G'
                          )

                      ) as a;

        RAISE NOTICE 'END FIRST INSERTION INTO BATCH_OUT_EFX TABLE';


-- STEP 2 UPDATE NAME_TYPE FIELD BASED ON BATCH_INSURANCE0_T TABLE WITH GURANTOR'S INFO WHEN CONDITION MET

	RAISE NOTICE 'START SECOND INSERTION INTO BATCH_OUT_EFX TABLE';

	INSERT INTO insurance0_batch_efx_out_t 
	SELECT  '',       
		'1',
		(CASE WHEN a.mname IS NOT NULL THEN a.fname || ' ' || a.mname || ' ' ||a.lname
                      ELSE                          a.fname || ' ' ||a.lname
                      END) as personal,
		a.addr1,        
		a.city,
		a.state,       
		a.zip5,
		a.zip4,       
		'',
		a.acctnum,       
		a.hid,
		'P',
		'', 
		a.cust_id::varchar(4),
		'',
		a.ssn,
                (CASE WHEN a.dob IS NULL THEN NULL
                      ELSE a.day_dob::varchar(2)||a.month_dob::varchar(2)||date_part('year',a.dob)::varchar(4)
                      END) as dob,
                a.month_rdate::varchar(2)||a.day_rdate::varchar(2)||a.year_rdate::varchar(2)||'_'||
                          a.hour_rdate::varchar(2)||a.min_rdate::varchar(2)||a.sec_rdate::varchar(2)||'_'||a.cid::varchar(6) as filestamp,
		''    
                FROM (
                       SELECT
                           *,
                           (CASE WHEN date_part('second',our_run_date) < 10 then '0'||date_part('second',our_run_date)::varchar(1)
                                 ELSE                                            date_part('second',our_run_date)::varchar(2)
                                 END) as sec_rdate,
                           (CASE WHEN date_part('minute',our_run_date) < 10 then '0'||date_part('minute',our_run_date)::varchar(1)
                                 ELSE                                           date_part('minute',our_run_date)::varchar(2)
                                 END) as min_rdate,
                           (CASE WHEN date_part('hour',our_run_date) < 10 then '0'||date_part('hour',our_run_date)::varchar(1)
                                 ELSE                                           date_part('hour',our_run_date)::varchar(2)
                                 END) as hour_rdate,
                           (CASE WHEN date_part('day',our_run_date) < 10 then '0'||date_part('day',our_run_date)::varchar(1)
                                 ELSE                                         date_part('day',our_run_date)::varchar(2)
                                 END) as day_rdate,
                           (CASE WHEN date_part('month',our_run_date) < 10 then '0'||date_part('month',our_run_date)::varchar(1)
                                 ELSE                                           date_part('month',our_run_date)::varchar(2)
                                 END) as month_rdate,
                           (date_part('year',our_run_date) - 2000) as year_rdate,
                           (CASE WHEN cust_id < 10     then '00000'||cust_id::varchar(1)
                                 WHEN cust_id < 100    then '0000'||cust_id::varchar(2)
                                 WHEN cust_id < 1000   then '000'||cust_id::varchar(3)
                                 WHEN cust_id < 10000  then '00'||cust_id::varchar(4)
                                 WHEN cust_id < 100000 then '0'||cust_id::varchar(5)
                                 ELSE                       cust_id::varchar(6)
                                 END) as cid,
                           (CASE WHEN date_part('day',dob) < 10 then '0'||date_part('day',dob)::varchar(1)
                                 ELSE                                  date_part('day',dob)::varchar(2)
                                 END) as day_dob,
                           (CASE WHEN date_part('month',dob) < 10 then '0'||date_part('month',dob)::varchar(1)
                                 ELSE                                    date_part('month',dob)::varchar(2)
                                 END) as month_dob

                       FROM
                          batch_insurance0_t
                       WHERE
                          (
                           (emdeon_commercial_flag = 'C'   OR emdeon_tpl_flag = 'C')
                            AND
                            name_type = 'P'
                          )
                          OR
                          emdeon_medicaid_flag = 'C' 
                          OR
                          emdeon_medicare_flag = 'C'

                      ) as a;

        RAISE NOTICE 'END SECOND INSERTION INTO BATCH_OUT_EFX TABLE';


  END IF;

RETURN v_efx_append;
END;
$$ LANGUAGE plpgsql;
